package org.lee.mysql;

import com.google.common.util.concurrent.ThreadFactoryBuilder;

import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.ThreadFactory;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @author lizhe
 */
public class PreparedStatementTest {

    private final static ThreadFactory THREAD_FACTORY = new ThreadFactoryBuilder().setNameFormat("thread-pool-%d").build();
    private final static int CORE_SIZE = Runtime.getRuntime().availableProcessors();
    private final static ThreadPoolExecutor THREAD_POOL_EXECUTOR = new ThreadPoolExecutor(CORE_SIZE,
            CORE_SIZE * 2, 60000, TimeUnit.MILLISECONDS,
            new LinkedBlockingQueue<>(1024), THREAD_FACTORY, new ThreadPoolExecutor.AbortPolicy());

    private static AtomicInteger count = new AtomicInteger(0);

    public static void main(String[] args) throws ClassNotFoundException, SQLException {


        final Connection connection = getConnection();

//        PreparedStatement preparedStatement = connection.prepareStatement("select * from student where name = ? limit 100");
//        preparedStatement.setString(1, "Jack");
//        ResultSet resultSet = preparedStatement.executeQuery();
//        TestJDBC.print(resultSet);
//
//        preparedStatement.setString(1, "Tom");
//        ResultSet resultSet2 = preparedStatement.executeQuery();
//        TestJDBC.print(resultSet2);
//
//        preparedStatement.close();

        count(connection);
        LocalDateTime now = LocalDateTime.now();
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String format = dtf.format(now);
        System.out.println(format);

        testBatchInsert();


        while (count.get() != 10) {

        }

        System.out.println(dtf.format(LocalDateTime.now()));
        count(connection);
        connection.close();

    }

    private static Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/blur?rewriteBatchedStatements=true";
        return DriverManager.getConnection(url, "root", "root");
    }

    private static void batchInsert() throws ClassNotFoundException, SQLException {
        Connection connection = getConnection();
        connection.setAutoCommit(false);
        String sql = "INSERT INTO `blur`.`t_order` (`order_no`, `user_id`, `shipping_id`, `payment`, `payment_type`, `postage`, `status`, `payment_time`, `send_time`, `end_time`, `close_time`, `create_time`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement insertStatement = connection.prepareStatement(sql);

        try {
            for (int i = 0; i < 100000; i++) {
                insertStatement.setLong(1, i);
                insertStatement.setInt(2, i);
                insertStatement.setInt(3, i);
                insertStatement.setBigDecimal(4, new BigDecimal(11.5d));
                insertStatement.setInt(5,1);
                insertStatement.setInt(6,5);
                insertStatement.setInt(7,20);
                insertStatement.setString(8,"2021-11-07 19:00:00");
                insertStatement.setString(9,"2021-11-07 19:00:00");
                insertStatement.setString(10,"2021-11-07 19:00:00");
                insertStatement.setString(11,"2021-11-07 19:00:00");
                insertStatement.setString(12,"2021-11-07 19:00:00");
                insertStatement.addBatch();

                if (i % 10000 == 0) {
                    insertStatement.executeBatch();
                    insertStatement.clearBatch();
                }
            }

            insertStatement.executeBatch();

        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            connection.commit();
//            connection.setAutoCommit(true);
            insertStatement.close();
            connection.close();
            count.getAndIncrement();
        }

    }


    private static void testBatchInsert() throws SQLException, ClassNotFoundException {


        for (int i = 0; i < 10; i++) {
            THREAD_POOL_EXECUTOR.submit(() -> {
                try {
                    batchInsert();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            });
        }
        THREAD_POOL_EXECUTOR.shutdown();

    }

    public static void count(Connection connection) throws SQLException {
        PreparedStatement countStatement = connection.prepareStatement("select count(*) as count from t_order");
        ResultSet countResult = countStatement.executeQuery();
        while (countResult.next()) {
            long count = countResult.getLong("count");
            System.out.println("count: " + count);
        }
        countStatement.close();
    }

}
